package com.example.demo.service.dao;

import com.example.demo.domain.Hotel;
import com.example.demo.domain.RoomType;
import org.springframework.stereotype.Repository;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.sql.*;
import java.util.List;

@Repository
public class HotelDao {
    public ArrayList<Hotel> findhotel(String dest,String key) {
        ArrayList<Hotel> hotels = new ArrayList<Hotel>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/hotel?serverTimezone=GMT", "root", "yanbinbin233");

            Statement statement = connection.createStatement();
            String sql = "select * from hotel where (province=? or city=?)  and (landmark like  \"%"+key+"%\" or attraction like \"%"+key+"%\" or hotel_name like \"%"+key+"%\")";
          //  System.out.println(sql);
            PreparedStatement state=connection.prepareStatement(sql);
            state.setString(1,dest);
            state.setString(2,dest);
           // state.setInt(3,star);
//            state.setString(4,key);
//            state.setString(5,key);
//            state.setString(6,key);
            ResultSet resultSet =state.executeQuery();

            while (resultSet.next()) {
                Hotel hotel= new Hotel();
                hotel.setAddress(resultSet.getString("address"));
                hotel.setAttraction(resultSet.getString("attraction"));
                hotel.setBreakfast(resultSet.getBoolean("breakfast"));
                hotel.setCity(resultSet.getString("city"));
                hotel.setHotelId(resultSet.getLong("hotel_id"));
                hotel.setCountry(resultSet.getString("country"));
                hotel.setHotelName(resultSet.getString("hotel_name"));
                hotel.setHotelTel(resultSet.getString("hotel_tel"));
                hotel.setLandmark(resultSet.getString("landmark"));
                hotel.setPrice(resultSet.getFloat("price"));
                hotel.setProvince(resultSet.getString("province"));
                hotel.setScore(resultSet.getFloat("score"));
                hotel.setStar(resultSet.getInt("star"));

               // System.out.println(resultSet.getString("hotel_name"));
                hotels.add(hotel);
            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hotels;
    }

    public List<Hotel> findhoteldetail(String dest,String key,int star,int breakfast,double score,double lowprice,double highprice) {
        ArrayList<Hotel> hotels = new ArrayList<Hotel>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/hotel?serverTimezone=GMT", "root", "yanbinbin233");

            Statement statement = connection.createStatement();
            String sql = "select * from hotel where (province=? or city=?)  and star=? and breakfast=? and score>=? and price>=? and price<=? and(landmark like  \"%"+key+"%\" or attraction like \"%"+key+"%\" or hotel_name like \"%"+key+"%\")"+"order by price asc";
           // System.out.println(sql);
            PreparedStatement state=connection.prepareStatement(sql);
            state.setString(1,dest);
            state.setString(2,dest);
            state.setInt(3,star);
            state.setInt(4,breakfast);
            state.setDouble(5,score);
            state.setDouble(6,lowprice);
            state.setDouble(7,highprice);
            ResultSet resultSet =state.executeQuery();

            while (resultSet.next()) {
                Hotel hotel= new Hotel();
                hotel.setAddress(resultSet.getString("address"));
                hotel.setAttraction(resultSet.getString("attraction"));
                hotel.setBreakfast(resultSet.getBoolean("breakfast"));
                hotel.setCity(resultSet.getString("city"));
                hotel.setHotelId(resultSet.getLong("hotel_id"));
                hotel.setCountry(resultSet.getString("country"));
                hotel.setHotelName(resultSet.getString("hotel_name"));
                hotel.setHotelTel(resultSet.getString("hotel_tel"));
                hotel.setLandmark(resultSet.getString("landmark"));
                hotel.setPrice(resultSet.getFloat("price"));
                hotel.setProvince(resultSet.getString("province"));
                hotel.setScore(resultSet.getFloat("score"));
                hotel.setStar(resultSet.getInt("star"));

               // System.out.println(resultSet.getString("hotel_name"));
                hotels.add(hotel);
            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hotels;
    }

    public Hotel findhotelbyid(long id) {
        Hotel hotel=new Hotel();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/hotel?serverTimezone=GMT", "root", "yanbinbin233");

            Statement statement = connection.createStatement();
            String sql = "select * from hotel where hotel_id=?";
            //  System.out.println(sql);
            PreparedStatement state=connection.prepareStatement(sql);
            state.setLong(1,id);
            ResultSet resultSet =state.executeQuery();
            while (resultSet.next()) {
                hotel.setAddress(resultSet.getString("address"));
                hotel.setAttraction(resultSet.getString("attraction"));
                hotel.setBreakfast(resultSet.getBoolean("breakfast"));
                hotel.setCity(resultSet.getString("city"));
                hotel.setHotelId(resultSet.getLong("hotel_id"));
                hotel.setCountry(resultSet.getString("country"));
                hotel.setHotelName(resultSet.getString("hotel_name"));
                hotel.setHotelTel(resultSet.getString("hotel_tel"));
                hotel.setLandmark(resultSet.getString("landmark"));
                hotel.setPrice(resultSet.getFloat("price"));
                hotel.setProvince(resultSet.getString("province"));
                hotel.setScore(resultSet.getFloat("score"));
                hotel.setStar(resultSet.getInt("star"));

                // System.out.println(resultSet.getString("hotel_name"));

            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return hotel;
    }

    public ArrayList<RoomType> findtypebyid(String id) {
        ArrayList<RoomType> roomtypes = new ArrayList<RoomType>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/hotel?serverTimezone=GMT", "root", "yanbinbin233");

            Statement statement = connection.createStatement();
            String realid="h"+id;
            String sql = "select * from roomtype where hotelID =?";
            System.out.println(sql);
            System.out.println(realid);
            PreparedStatement state=connection.prepareStatement(sql);
            state.setString(1,realid);
            ResultSet resultSet =state.executeQuery();

            while (resultSet.next()) {
                System.out.println("有一个！");
                RoomType roomtype=new RoomType();
                roomtype.setArea(resultSet.getInt("area"));
                roomtype.setBreakfast(resultSet.getString("breakfast"));
                roomtype.setDescription(resultSet.getString("description"));
                roomtype.setDevices(resultSet.getString("devices"));
                roomtype.setPrice(resultSet.getFloat("price"));
                roomtype.setSpecialroomtype(resultSet.getString("specialRoomType"));
                roomtype.setHotelid(resultSet.getString("hotelID"));
                roomtype.setRoomid(resultSet.getString("roomID"));
                roomtype.setHotelname(resultSet.getString("hotelName"));


                System.out.println(resultSet.getString("description"));
                roomtypes.add(roomtype);
            }
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return roomtypes;
    }
}
